-- FlowPlan数据验证脚本
-- 说明: 提供数据完整性和一致性的验证脚本

-- 1. 数据完整性检查函数
CREATE OR REPLACE FUNCTION validate_data_integrity()
RETURNS TABLE(
    check_name VARCHAR(100),
    check_result BOOLEAN,
    check_message TEXT
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 检查用户表中的外键约束
    RETURN QUERY
    SELECT 
        '用户组织关联检查' as check_name,
        COUNT(*) = 0 as check_result,
        CASE 
            WHEN COUNT(*) = 0 THEN '所有用户都有有效的组织关联'
            ELSE '发现 ' || COUNT(*) || ' 个用户没有有效的组织关联'
        END as check_message
    FROM sys_user u
    LEFT JOIN sys_org o ON u.org_id = o.id
    WHERE o.id IS NULL;
    
    -- 检查项目表中的外键约束
    RETURN QUERY
    SELECT 
        '项目创建者关联检查' as check_name,
        COUNT(*) = 0 as check_result,
        CASE 
            WHEN COUNT(*) = 0 THEN '所有项目都有有效的创建者关联'
            ELSE '发现 ' || COUNT(*) || ' 个项目没有有效的创建者关联'
        END as check_message
    FROM project p
    LEFT JOIN sys_user u ON p.creator_id = u.id
    WHERE u.id IS NULL;
    
    -- 检查待办表中的外键约束
    RETURN QUERY
    SELECT 
        '待办项目关联检查' as check_name,
        COUNT(*) = 0 as check_result,
        CASE 
            WHEN COUNT(*) = 0 THEN '所有待办都有有效的项目关联'
            ELSE '发现 ' || COUNT(*) || ' 个待办没有有效的项目关联'
        END as check_message
    FROM todo t
    LEFT JOIN project p ON t.project_id = p.id
    WHERE p.id IS NULL;
    
    -- 检查重复的用户名
    RETURN QUERY
    SELECT 
        '用户名唯一性检查' as check_name,
        COUNT(*) = 0 as check_result,
        CASE 
            WHEN COUNT(*) = 0 THEN '所有用户名都是唯一的'
            ELSE '发现 ' || COUNT(*) || ' 个重复的用户名'
        END as check_message
    FROM (
        SELECT username, COUNT(*) as cnt
        FROM sys_user
        GROUP BY username
        HAVING COUNT(*) > 1
    ) duplicates;
    
    -- 检查重复的邮箱
    RETURN QUERY
    SELECT 
        '邮箱唯一性检查' as check_name,
        COUNT(*) = 0 as check_result,
        CASE 
            WHEN COUNT(*) = 0 THEN '所有邮箱都是唯一的'
            ELSE '发现 ' || COUNT(*) || ' 个重复的邮箱'
        END as check_message
    FROM (
        SELECT email, COUNT(*) as cnt
        FROM sys_user
        GROUP BY email
        HAVING COUNT(*) > 1
    ) duplicates;
    
    -- 检查无效的枚举值
    RETURN QUERY
    SELECT 
        '用户角色有效性检查' as check_name,
        COUNT(*) = 0 as check_result,
        CASE 
            WHEN COUNT(*) = 0 THEN '所有用户角色值都有效'
            ELSE '发现 ' || COUNT(*) || ' 个无效的用户角色值'
        END as check_message
    FROM sys_user
    WHERE role NOT IN ('ADMIN', 'DEVELOPER', 'DEMANDER', 'TESTER', 'DBA');
    
    RETURN QUERY
    SELECT 
        '项目状态有效性检查' as check_name,
        COUNT(*) = 0 as check_result,
        CASE 
            WHEN COUNT(*) = 0 THEN '所有项目状态值都有效'
            ELSE '发现 ' || COUNT(*) || ' 个无效的项目状态值'
        END as check_message
    FROM project
    WHERE status NOT IN ('PLANNING', 'IN_PROGRESS', 'TESTING', 'COMPLETED', 'CANCELLED');
    
    RETURN QUERY
    SELECT 
        '待办状态有效性检查' as check_name,
        COUNT(*) = 0 as check_result,
        CASE 
            WHEN COUNT(*) = 0 THEN '所有待办状态值都有效'
            ELSE '发现 ' || COUNT(*) || ' 个无效的待办状态值'
        END as check_message
    FROM todo
    WHERE status NOT IN ('TODO', 'IN_PROGRESS', 'DONE', 'BLOCKED');
END;
$$;

-- 2. 数据一致性检查存储过程
CREATE OR REPLACE PROCEDURE check_data_consistency(
    OUT p_inconsistencies_found BOOLEAN,
    OUT p_inconsistency_details TEXT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_count INTEGER;
    v_details TEXT := '';
BEGIN
    p_inconsistencies_found := FALSE;
    
    -- 检查已完成的待办是否有实际工时
    SELECT COUNT(*) INTO v_count
    FROM todo 
    WHERE status = 'DONE' AND actual_hours IS NULL;
    
    IF v_count > 0 THEN
        p_inconsistencies_found := TRUE;
        v_details := v_details || '发现 ' || v_count || ' 个已完成的待办没有填写实际工时;' || CHR(10);
    END IF;
    
    -- 检查预估工时为负数的情况
    SELECT COUNT(*) INTO v_count
    FROM todo 
    WHERE estimated_hours < 0;
    
    IF v_count > 0 THEN
        p_inconsistencies_found := TRUE;
        v_details := v_details || '发现 ' || v_count || ' 个待办的预估工时为负数;' || CHR(10);
    END IF;
    
    -- 检查实际工时超过预估工时过多的情况（超过2倍）
    SELECT COUNT(*) INTO v_count
    FROM todo 
    WHERE estimated_hours > 0 AND actual_hours > estimated_hours * 2;
    
    IF v_count > 0 THEN
        p_inconsistencies_found := TRUE;
        v_details := v_details || '发现 ' || v_count || ' 个待办的实际工时超过预估工时的2倍;' || CHR(10);
    END IF;
    
    -- 检查截止日期早于创建日期的情况
    SELECT COUNT(*) INTO v_count
    FROM todo 
    WHERE due_date IS NOT NULL AND due_date < create_time;
    
    IF v_count > 0 THEN
        p_inconsistencies_found := TRUE;
        v_details := v_details || '发现 ' || v_count || ' 个待办的截止日期早于创建日期;' || CHR(10);
    END IF;
    
    p_inconsistency_details := v_details;
END;
$$;

-- 3. 数据清理存储过程
CREATE OR REPLACE PROCEDURE cleanup_invalid_data()
LANGUAGE plpgsql
AS $$
DECLARE
    v_deleted_count INTEGER;
BEGIN
    -- 删除没有关联项目的Excel计划
    DELETE FROM excel_plan 
    WHERE project_id NOT IN (SELECT id FROM project);
    GET DIAGNOSTICS v_deleted_count = ROW_COUNT;
    RAISE NOTICE '删除了 % 个没有关联项目的Excel计划', v_deleted_count;
    
    -- 删除没有关联待办的Excel关联记录
    DELETE FROM excel_todo_rel 
    WHERE todo_id NOT IN (SELECT id FROM todo) OR excel_id NOT IN (SELECT id FROM excel_plan);
    GET DIAGNOSTICS v_deleted_count = ROW_COUNT;
    RAISE NOTICE '删除了 % 个无效的Excel关联记录', v_deleted_count;
    
    -- 删除没有关联项目或待办的笔记
    DELETE FROM note 
    WHERE (project_id IS NOT NULL AND project_id NOT IN (SELECT id FROM project))
       OR (todo_id IS NOT NULL AND todo_id NOT IN (SELECT id FROM todo));
    GET DIAGNOSTICS v_deleted_count = ROW_COUNT;
    RAISE NOTICE '删除了 % 个无效的笔记记录', v_deleted_count;
    
    -- 修正已完成但没有实际工时的待办
    UPDATE todo 
    SET actual_hours = estimated_hours * 0.8  -- 假设实际工时为预估工时的80%
    WHERE status = 'DONE' AND actual_hours IS NULL;
    GET DIAGNOSTICS v_deleted_count = ROW_COUNT;
    RAISE NOTICE '修正了 % 个已完成但没有实际工时的待办', v_deleted_count;
END;
$$;